﻿//
// (C) Copyright 2009 Irantha Suwandarathna (irantha@gmail.com)
// All rights reserved.
//

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using EffiProzDB;
using EffiProzDB.Lib;
using System.Threading;

namespace EffiProzDBTest.SelfTest
{
    /// <summary>
    /// Summary description for TestTextTableC:\Users\irantha\Documents\Visual Studio 10\Projects\EffiProz\EffiProzDBTest\SelfTest\TestTextTable.cs
    /// </summary>
    [TestClass]
    public class TestTextTableHarnes
    {
        //SessionInterface sessionProxy;
       // String user = "sa";
        //String password = "";
       // String connType = DatabaseURL.S_FILE;
        // String host = "localhost";
        //int port = 0;
        //String path = "Test";
        //String database = "Test";
        //string sql = "";
        HsqlProperties props = new HsqlProperties();
       // string testScriptPath = "../../../EffiProzDBTest/SelfTest/Scripts/";
        string dataPath = "../../../EffiProzDBTest/Data/";
        TestTextTable testTextTable;

        public TestTextTableHarnes()
        {
            CleanUp("Texttbl");
            testTextTable = new TestTextTable();
        }



        private TestContext testContextInstance;

        /// <summary>
        ///Gets or sets the test context which provides
        ///information about and functionality for the current test run.
        ///</summary>
        public TestContext TestContext
        {
            get
            {
                return testContextInstance;
            }
            set
            {
                testContextInstance = value;
            }
        }

        public void CleanUp(string prefix)
        {
            string[] files = Directory.GetFiles(dataPath, prefix + "*");
            foreach (var file in files)
                File.Delete(file);
            File.Delete(dataPath + "customers.csv");
            File.Delete(dataPath + "products.csv");
            File.Delete(dataPath + "malformed.csv");
        }

        #region Additional test attributes
        //
        // You can use the following additional attributes as you write your tests:
        //
        // Use ClassInitialize to run code before running the first test in the class
        // [ClassInitialize()]
        // public static void MyClassInitialize(TestContext testContext) { }
        //
        // Use ClassCleanup to run code after all tests in a class have run
        // [ClassCleanup()]
        // public static void MyClassCleanup() { }
        //
        // Use TestInitialize to run code before running each test 
        // [TestInitialize()]
        // public void MyTestInitialize() { }
        //
        // Use TestCleanup to run code after each test has run
        // [TestCleanup()]
        // public void MyTestCleanup() { }
        //
        #endregion

        [TestMethod]
        public void TextTable()
        {        
            testTextTable.setUp();
            testTextTable.verifyInitialContent();          
            testTextTable.checkSeparators();
            testTextTable.checkSourceConnection();
            testTextTable.TestInsertDeleteUpdate();

        }
    }

    internal class TestTextTable
    {

        SessionInterface m_connection;

        TextTableDescriptor m_products;
        TextTableDescriptor m_customers;
        TextTableDescriptor m_test;

        String user = "sa";
        String password = "";
        String connType = DatabaseURL.S_FILE;
       //  String host = "localhost";
       // int port = 0;
       // String path = "Texttbl";
        String database = "TextTbl";
       // string sql = "";
        HsqlProperties props = new HsqlProperties();
        // string testScriptPath = "../../../EffiProzDBTest/SelfTest/Scripts/";
        internal string dataPath = "../../../EffiProzDBTest/Data/";

       private void newSession(){
           m_connection = DatabaseManager.newSession(connType, dataPath+database,
           user, password, props);
       }

        /** Creates a new instance of TestTextTable */
        public TestTextTable()
        {
            newSession();

            m_products = new TextTableDescriptor(
                                               this,
                                               "products",
                                               "ID INTEGER PRIMARY KEY, \"name\" VARCHAR(20)",
                                               "\t",
                                               "\\t",
                                               new Object[,] {
                                                {  1 , "Apples" },
                                                {  2 , "Oranges" }
                                            });

            m_test = new TextTableDescriptor(
                                               this,
                                               "test",
                                               "ID BIGINT, \"dateval\" DATETIME, \"doubleval\" DOUBLE,\"bval\" BIT ",                                           
                                               "\t",
                                               "\\t",
                                               new Object[,] {
                                                {  1L , DateTime.Now,34.56,true },                                             
                                            });

            m_customers = new TextTableDescriptor(
                                                 this,
                                                "customers",
                                                "ID INTEGER PRIMARY KEY,"
                                                    + "\"name\" VARCHAR(50),"
                                                    + "\"address\" VARCHAR(50),"
                                                    + "\"city\" VARCHAR(50),"
                                                    + "\"postal\" VARCHAR(50)",
                                                ";",
                                                "\\semi",
                                                new Object[,] {
                                                 {  1 , "Food, Inc.", "Down Under", "Melbourne", "509" },
                                                 { 2 , "Simply Delicious", "Down Under", "Melbourne", "518" },
                                                 {  3 , "Pure Health", "10 Fish St.", "San Francisco", "94107" }
                                            });
        }

        public void TestInsertDeleteUpdate()
        {
            string sql = "INSERT INTO \"" + m_products.getName() + "\" VALUES(7,'pineapple') ; INSERT INTO \"" + m_products.getName() + "\"  VALUES(9,'mango') ;";
            Result res = TestHelper.ExecuteUpdateSQL_Success(m_connection, sql);
            sql = "UPDATE \"" + m_products.getName() + "\" SET \"name\"='lunawa' WHERE ID=7;" ;
            res = TestHelper.ExecuteUpdateSQL_Success(m_connection, sql);
            sql = "UPDATE \"" + m_products.getName() + "\" SET \"ID\"=101 WHERE ID=7;";
            res = TestHelper.ExecuteUpdateSQL_Success(m_connection, sql);
            sql = "DELETE FROM \"" + m_products.getName() + "\" WHERE ID=7;";
            res = TestHelper.ExecuteUpdateSQL_Success(m_connection, sql);

            TestHelper.ExecuteSelectSQL(m_connection, "SELECT * FROM \"test\"", 1);
        }

        /** sets up all text files for the test database
         */
        private void setupTextFiles()
        {

            m_products.createTextFile();
            m_customers.createTextFile();
            m_test.createTextFile();
        }

        /** creates the database tables needed for the test
         */
        private void setupDatabase()
        {

            try
            {
                m_products.createTable(m_connection);
                m_customers.createTable(m_connection);
                m_test.createTable(m_connection);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message + " " + ex.StackTrace);
            }
        }

        public void setUp()
        {
            setupTextFiles();
            setupDatabase();
        }

        protected void tearDown()
        {
            TestHelper.ExecuteUpdateSQL_Success(m_connection, "SHUTDOWN");
        }

        /** returns the data source definition for a given text table
         */
        internal String getDataSourceSpec(String tableName)
        {
            String spec = null;
            try
            {
                string sql = "SELECT DATA_SOURCE_DEFINTION FROM INFORMATION_SCHEMA.SYSTEM_TEXTTABLES "
                     + "WHERE TABLE_NAME='" + tableName + "'";
                Result result = TestHelper.ExecuteSelectSQL(m_connection, sql, null);
                if (result.isError())
                    throw new Exception(result.mainString);

                object[] data = (object[])result.iterator().next();

                spec = (string)data[0];

            }
            catch (Exception ex)
            {
                Assert.Fail("getDataSourceSpec(" + tableName + ") failed: " + ex.Message);
            }
            return spec;
        }

        /** determines whether a given table is currently read-only
         */
        internal bool isReadOnly(String tableName)
        {
            bool isReadOnly = true;
            try
            {

                string sql = "SELECT READ_ONLY FROM INFORMATION_SCHEMA.SYSTEM_TABLES "
                    + "WHERE TABLE_NAME='" + m_products.getName() + "'";

                Result result = TestHelper.ExecuteSelectSQL(m_connection, sql, null);
                if (result.isError())
                    throw new Exception(result.mainString);

                object[] data = (object[])result.iterator().next();

                isReadOnly = (bool)data[0];

            }
            catch (Exception ex)
            {
                Assert.Fail("isReadOnly(" + tableName + ") failed: " + ex.Message);
            }
            return isReadOnly;
        }

        /** checks different field separators
         */
        internal void checkSeparators()
        {

            String[,] separators = new String[,] {
            // special separators
             { ";", "\\semi" },
             { "\"", "\\quote" },
             { " ", "\\space" },
             { "'", "\\apos" },
            //new String[] { "\n", "\\n" },
                // doesn't work as expected - seems I don't understand how this is intended to work?
            { "\t", "\\t" },
            { "\\", "\\" },
            // some arbitrary separators which need not to be escaped
            { ".", "." },
            { "-", "-" },
            { "#", "#" },
             { ",", "," }
            // unicode character
            //new String[] { "\u1234", "\\u1234" }
                // doesn't work. How do I specify in a FileOutputStream which encoding to use when writing
                // strings?
        };

            for (int i = 0; i < separators.GetLength(0); ++i)
            {
                String separator = separators[i,0];
                String separatorSpec = separators[i,1];

                // create the file
                String tableName = "customers_" + i;
                TextTableDescriptor tempCustomersDesc = new TextTableDescriptor(this,
                    tableName, m_customers.getColumnSpec(), separator, separatorSpec, m_customers.getData());
                tempCustomersDesc.createTextFile();

                try
                {
                    tempCustomersDesc.createTable(m_connection);
                }
                catch (Exception t)
                {
                    Assert.Fail("checkSeparators: separator '" + separatorSpec + "' doesn't work: " + t.Message);
                }

                TestHelper.ExecuteUpdateSQL_Success(m_connection,"SET TABLE \"" + tableName + "\" SOURCE OFF");
                TestHelper.ExecuteUpdateSQL_Success(m_connection, "DROP TABLE \"" + tableName + "\"");
            }
        }

        /** verifies the content of a given table is as expected
         *  @param tableName
         *      the name of the table whose content is to check
         *  @param expectedValues
         *      the values expected in the table
         */
        private void verifyTableContent(String tableName, Object[,] expectedValues)
        {

            String selectStmt = "SELECT * FROM \"" + tableName + "\" ORDER BY ID";
            try
            {
                Result results = TestHelper.ExecuteSelectSQL(m_connection, selectStmt,null);
                TestHelper.MatchMultiResult(results,expectedValues);        
            }
             catch (Exception t)
            {
                Assert.Fail("verifyTableContent(" + tableName + ") failed with " + t.Message);
            }
        }

      

        /** verifies the initial content of the "products" text table, plus a simple insertion
         */
        internal void verifyInitialContent()
        {

            verifyTableContent(m_products.getName(), m_products.getData());
            verifyTableContent(m_customers.getName(), m_customers.getData());
        }

        /** does some very basic insertion tests
         */
        private void checkInsertions()
        {

            // check whether inserting a value succeeds
            TestHelper.ExecuteUpdateSQL_Success(m_connection, "INSERT INTO \"" + m_products.getName() + "\" VALUES ( 3, 'Pears' )");
            verifyTableContent(m_products.getName(),
                m_products.appendRowData(new Object[] {3, "Pears" }));

            // check whether the PK constraint works
           
            TestHelper.ExecuteUpdateSQL_Failure(m_connection,"INSERT INTO \"" + m_products.getName() + "\" VALUES ( 1, 'Green Apples' )");
              
        }

        /** verifies whether implicit and explicit dis/connections from/to the text table source work
         *  as expected
         */
        internal void checkSourceConnection()
        {
            String sqlSetTable = "SET TABLE \"" + m_products.getName() + "\"";
            // preconditions for the following tests
            Assert.AreEqual( m_products.getDataSourceSpec(), getDataSourceSpec(m_products.getName()) ,"internal error: retrieving the data source does not work properly at all."
               );
            Assert.IsFalse(isReadOnly(m_products.getName()) , "internal error: table should not be read-only, initially");

            // disconnect, see if the table behaves well afterwards
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " SOURCE OFF");
            Assert.AreEqual(m_products.getDataSourceSpec(), getDataSourceSpec(m_products.getName()), "Disconnecting a text table should not reset the table source."
                );
            Assert.IsTrue(isReadOnly(m_products.getName()) ,"Disconnecting from the table source should put the table into read-only mode."
                );

          
            //A disconnected table should be empty.
            Result result = TestHelper.ExecuteSelectSQL(m_connection, "SELECT * FROM \"" + m_products.getName() + "\"" , 0);
            
          

            // reconnect, see if the table works as expected then
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " SOURCE ON");
            verifyTableContent(m_products.getName(), m_products.getData());

            // check whether dis-/reconnecting a readonly table preserves the readonly-ness
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " READONLY TRUE");
            Assert.IsTrue( isReadOnly(m_products.getName()) ,"Setting the table to read-only failed.");
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " SOURCE OFF");
            Assert.IsTrue( isReadOnly(m_products.getName()), "Still, a disconnected table should be read-only." );
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " SOURCE ON");
            Assert.IsTrue(isReadOnly(m_products.getName()) , "A reconnected readonly table should preserve its readonly-ness.");
            TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " READONLY FALSE");
            Assert.IsFalse(isReadOnly(m_products.getName()), "Unable to reset the readonly-ness.");

            // check whether setting an invalid data source sets the table to readonly, by
            // preserving the data source
            try
            {
                // create a malformed file
                String fileName = "malformed.csv";
                StreamWriter textFile = new StreamWriter(new FileStream(dataPath + fileName, FileMode.OpenOrCreate, FileAccess.Write));
                
                textFile.WriteLine("not a number;some text");
                textFile.Close();
             
                // try setting it as source
                String newDataSourceSpec = fileName + ";encoding=UTF-8;fs=\\semi";
                
               TestHelper.ExecuteUpdateSQL_Failure(m_connection, sqlSetTable + " SOURCE \"" + newDataSourceSpec + "\"");
                   
               
              
                //Assert.IsTrue( isReadOnly(m_products.getName()), "A table with an invalid data source should fall back to read-only.");
                Assert.AreEqual(newDataSourceSpec,  getDataSourceSpec(m_products.getName()), "A data source which cannot be set should nonetheless be remembered."
                   );

                // the data source spec should even survive a shutdown
                TestHelper.ExecuteUpdateSQL_Success(m_connection, "SHUTDOWN");

                newSession();

                Assert.AreEqual(newDataSourceSpec, getDataSourceSpec(m_products.getName()),
                    "A data source pointing to a mailformed file should survive a database shutdown.");
                Assert.IsTrue( isReadOnly(m_products.getName()), "After shutdown and DB-reconnect, the table with a malformed source should be read-only, again." );

                // reconnect after fixing the file
                textFile = new StreamWriter(new FileStream(dataPath + fileName, FileMode.Truncate, FileAccess.Write));
                textFile.WriteLine("1;some text");
                textFile.Close();

                TestHelper.ExecuteUpdateSQL_Success(m_connection, sqlSetTable + " SOURCE ON");
                Assert.IsFalse( isReadOnly(m_products.getName()), "The file was fixed, reconnect was successful, so the table shouldn't be read-only."
                   );

                // finally re-create the proper version of the table for any further tests
                m_products.createTextFile();
                m_products.createTable(m_connection);
                verifyTableContent(m_products.getName(), m_products.getData());
            }
             catch (Exception t)
            {
                Assert.Fail("checkSourceConnection: unable to check invalid data sources, error: " + t.Message);
            }
        }

        internal class TextTableDescriptor
        {
            private String m_name;
            private String m_columnSpec;
            private String m_separator;
            private String m_separatorSpec;
            private Object[,] m_data;
            private TestTextTable _o;

            public TextTableDescriptor(TestTextTable _o,String name, String columnSpec, String separator, String separatorSpec, Object[,] data)
            {
                m_name = name;
                m_columnSpec = columnSpec;
                m_separator = separator;
                m_separatorSpec = separatorSpec;
                m_data = data;
                this._o = _o;
            }

            public String getName() { return m_name; }
            public String getColumnSpec() { return m_columnSpec; }
            public String getSeparator() { return m_separator; }
            public String getSeparatorSpec() { return m_separatorSpec; }
            public Object[,] getData() { return m_data; }

            public Object[,] appendRowData(Object[] rowData)
            {
                Object[,] newData = new Object[m_data.Length + 1, rowData.Length];

                for (int i = 0; i < m_data.GetLength(0); i++)
                {
                    for (int j = 0; j < m_data.GetLength(1); j++)
                    {
                        newData[i, j] = m_data[i, j];
                    }
                }

                for (int col = 0; col < rowData.Length; ++col)
                    m_data[m_data.GetLength(0) - 1, col] = rowData[col];
  
                return m_data;
            }

            /** creates a text file as described by this instance
             */
            internal void createTextFile()
            {
                StreamWriter textFile = null;

                try
                {
                    String completeFileName = _o.dataPath + m_name + ".csv";
                    File.Delete(completeFileName);
                    textFile = new StreamWriter(new FileStream(completeFileName, FileMode.OpenOrCreate, FileAccess.Write));
                }
                catch (Exception ex)
                {
                    Assert.Fail(ex.Message);
                }

                for (int row = 0; row < m_data.GetLength(0); ++row)
                {

                    StringBuilder buf = new StringBuilder();
                    int colCount = m_data.GetLength(1);
                    for (int col = 0; col < colCount; ++col)
                    {

                        buf.Append(m_data[row,col]);
                        if (col + 1 != colCount)
                            buf.Append(m_separator);
                    }
                    textFile.WriteLine(buf.ToString());
                }
                textFile.Close();
            }

            internal String getDataSourceSpec()
            {
               
                return  m_name + ".csv;encoding=UTF-8;fs=" + m_separatorSpec;
            }

            internal void createTable(SessionInterface sessionInterface)
            {
                String createTable = "DROP TABLE \"" + m_name + "\" IF EXISTS;";
                createTable += "CREATE TEXT TABLE \"" + m_name + "\" ( " + m_columnSpec + " );";

                TestHelper.ExecuteUpdateSQL_Success(sessionInterface, createTable);

                bool test = _o.isReadOnly(m_name);

                String setTableSource = "SET TABLE \"" + m_name + "\" SOURCE \"" + getDataSourceSpec() + "\"";
                TestHelper.ExecuteUpdateSQL_Success(sessionInterface, setTableSource);

            }
        }
    }
}
